



INSERT INTO product (product_code , product_name)
 VALUES 
 ('01','服装'),
 ('0101','男装'),
 ('010101','西装'),
 ('010102','休闲装'),
 ('0102','女装'),
 ('010201','套装'),
 ('010202','职业装'),
 ('010203','休闲装'),
 ('0103','童装'),
 ('02','电器'),
 ('0201','进口'),
 ('0202','国产'),
 ('03','日用品');

INSERT INTO product2 ( code,pid,[name])
 VALUES 
 ('01','0','服装'),
 ('01','1','男装'),
 ('01','2','西装'),
 ('02','2','休闲装'),
 ('01','1','女装'),
 ('01','5','套装'),
 ('02','5','职业装'),
 ('03','5','休闲装'),
 ('03','1','童装'),
 ('02','0','电器'),
 ('01','10','进口'),
 ('02','10','国产'),
 ('03','0','日用品');


create proc GET_DIR
@code nvarchar(50)
as


begin
    -- @code 是输入的要查询的编号
    -- @codelen 编号的长度
    -- @full_dir 返回的字符串,显示节点的路径
    -- @temp 两个循环中的临时变量
    -- PRINT concat('code is: ', @code)
    DECLARE @codelen INT
    SET @codelen = LEN(@code)
    -- PRINT concat('len of code is: ', @codelen)
    Declare @full_dir nvarchar(200)
    WHILE    @codelen > 0
    BEGIN  
        DECLARE @tempcode nvarchar(50)
        DECLARE @tempname nvarchar(50)
        SET @tempcode = substring(@code,1,@codelen)
        select @tempname = product_name from product where product_code = @tempcode
        SET @full_dir = CONCAT(@full_dir,'<',@tempname)
        SET @codelen = @codelen - 2
    END 
    SET @full_dir =SUBSTRING(@full_dir , 2 , LEN(@full_dir))
    PRINT @full_dir
end

测试:
GET_DIR '0202'
返回结果:
国产<电器
